package dbUtil

import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"strconv"
)

// 使用原生SQL语句
// go-sql-driver地址：https://github.com/go-sql-driver/mysql
var sqlDb *sql.DB //数据库连接db

// 初始化
func init() {
	//打开数据库
	//parseTime:时间格式转换(查询结果为时间时，是否自动解析为时间);
	//loc=Local：MySQL的时区设置
	sqlStr := "root:rx123456@tcp(127.0.0.1:3306)/gin_demo?charset=utf8&parseTime=true&loc=Local"
	var err error
	sqlDb, err = sql.Open("mysql", sqlStr)
	if err != nil {
		fmt.Println("数据库打开失败：", err)
		return
	}

	//测试与数据库建立的连接，非必要（校验连接是否正确）
	err = sqlDb.Ping()
	if err != nil {
		fmt.Println("数据库连接失败：", err)
		return
	}
}

// 用户结构体
type sqlUserData struct {
	Id      int    `json:"id"`
	UserNo  int    `json:"user_no"`
	Name    string `json:"name"`
	Age     int    `json:"age"`
	Address string `json:"address"`
	Remarks string `json:"remarks"`
}

func MysqlGetUserList(c *gin.Context) {
	name := c.Query("name")
	sqlStr := "select id,user_no,name,age,address from " + tableName + " where name=?"
	rows, err := sqlDb.Query(sqlStr, name)
	if err != nil {
		showError(c, fmt.Sprint(err))
		return
	}
	defer rows.Close()
	userList := make([]sqlUserData, 0)
	for rows.Next() {
		var user sqlUserData
		rows.Scan(&user.Id, &user.UserNo, &user.Name, &user.Age, &user.Address)
		if user.Age >= 18 {
			user.Remarks = "已成年"
		} else {
			user.Remarks = "未成年"
		}
		userList = append(userList, user)
	}
	showSuccess(c, userList)
}

func MysqlGetUserInfo(c *gin.Context) {
	id := c.Query("id")
	sqlStr := "select id,user_no,name,age,address from " + tableName + " where id=?"
	var user sqlUserData
	err := sqlDb.QueryRow(sqlStr, id).Scan(&user.Id, &user.UserNo, &user.Name, &user.Age, &user.Address)
	if err != nil {
		showError(c, fmt.Sprint(err))
		return
	}
	showSuccess(c, user)
}

func MysqlInsertData(c *gin.Context) {
	/*
		//请求参数
		{
			"user_no":1001,
			"name":"张三",
			"age":18,
			"address":"北京昌平"
		}
	*/
	var user sqlUserData
	err := c.Bind(&user)
	if err != nil {
		showError(c, fmt.Sprint(err))
		return
	}
	sqlStr := "insert into " + tableName + "(user_no, name, age, address) values (?,?,?,?)"
	ret, err := sqlDb.Exec(sqlStr, user.UserNo, user.Name, user.Age, user.Address)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		showError(c, fmt.Sprint(err))
		return
	}
	newDataId, _ := ret.LastInsertId()
	showSuccess(c, "新增的结果id："+strconv.Itoa(int(newDataId)))
}

func MysqlUpdateData(c *gin.Context) {
	/*
		//请求参数
		{
		    "id":1,
		    "age":13,
		    "address":"河北"
		}
	*/
	var user sqlUserData
	err := c.Bind(&user)
	if err != nil {
		showError(c, fmt.Sprint(err))
		return
	}
	sqlStr := "update " + tableName + " set age=? ,address=? where id=?"
	ret, err := sqlDb.Exec(sqlStr, user.Age, user.Address, user.Id)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		showError(c, fmt.Sprint(err))
		return
	}
	res, _ := ret.RowsAffected()
	showSuccess(c, "受影响的行数："+strconv.Itoa(int(res)))
}

func MysqlDeleteData(c *gin.Context) {
	id := c.Query("id")
	var count int
	//先查询
	sqlStr := "select count(*) from " + tableName + " where id=?"
	err := sqlDb.QueryRow(sqlStr, id).Scan(&count)
	if count <= 0 || err != nil {
		showError(c, "数据不存在")
		return
	}

	//再删除
	delStr := "delete from " + tableName + " where id=?"
	ret, err := sqlDb.Exec(delStr, id)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		showError(c, fmt.Sprint(err))
		return
	}
	res, _ := ret.RowsAffected()
	showSuccess(c, "受影响的行数："+strconv.Itoa(int(res)))
}
